﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExportToDWGTool
{
    class NPOIExcelHelper
    {
        //读取excel
        public static IWorkbook OpenWorkbook(string path)
        {
            FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Write);
            NPOI.SS.UserModel.IWorkbook workbook = WorkbookFactory.Create(fileStream);

            return workbook;
        }
        /// <summary>
        /// search first or default excel worksheet whose name is started with pattern(在工作簿中查找以pattern开头的工作表)
        /// </summary>
        /// <param name="workbook">要查找的工作簿</param>
        /// <param name="pattern">指定开头字符串</param>
        /// <returns></returns>
        public static ISheet FirstOrDefaultWorksheetNameStartWith(IWorkbook workbook, string pattern)
        {
            NPOI.SS.UserModel.ISheet worksheet = null;
            var length = workbook.NumberOfSheets;
            for (int i = 0; i < length; i++)
            {
                worksheet = workbook.GetSheetAt(i);
                if (worksheet.SheetName.StartsWith(pattern))
                    break;
                worksheet = null;
            }
            return worksheet;
        }

        /// <summary>
		/// read excel worksheet
		/// </summary>
		/// <param name="worksheet">要读取的工作表</param>
		/// <param name="rows">指定读取多少行</param>
		/// <param name="columns">指定读取多少列</param>
		/// <param name="startIndex">通常前3行是:注释,字段名和字段类型,如果注释全部不填,该行也应该读取</param>
		/// <returns></returns>
		public static List<string[]> ReadLines(ISheet worksheet, int rows = 0, int columns = 0, int startIndex = 1)
        {
            if (rows <= 0)
                rows = worksheet.LastRowNum + 1;
            else
                rows = Math.Min(rows, worksheet.LastRowNum + 1);
            if (rows == 0)
                return new List<string[]>();

            int rownum = startIndex;
            IRow row = worksheet.GetRow(rownum);
            if (columns <= 0)
                columns = row.LastCellNum + 1;
            else
                columns = Math.Min(columns, row.LastCellNum + 1);
            //List<>的每一个元素是一行数据,string[]的每一个元素是对应单元格的string值
            List<string[]> lines = new List<string[]>(rows);
            while (rownum < rows)
            {
                if (rownum < startIndex)
                {
                    rownum++;
                    row = worksheet.GetRow(rownum);
                    continue;
                }
                if (row != null)
                {
                    bool add = false;
                    var line = new string[columns];
                    for (int i = 0; i < columns; i++)
                    {
                        var cell = row.GetCell(i);
                        if (cell != null)
                        {
                            if (cell.CellType != CellType.Formula)
                                line[i] = cell.ToString().Trim();
                            else
                            {
                                if (cell.CachedFormulaResultType == CellType.Numeric)
                                    line[i] = cell.NumericCellValue.ToString();
                                else if (cell.CachedFormulaResultType == CellType.String)
                                    line[i] = cell.StringCellValue.Trim();
                                else if (cell.CachedFormulaResultType == CellType.Boolean)
                                    line[i] = cell.BooleanCellValue.ToString();
                            }
                        }
                        else
                        {
                            line[i] = null;
                        }
                        if (!string.IsNullOrEmpty(line[i]))
                            add = true;
                    }
                    if (add)
                        lines.Add(line);
                }
                else
                {
                    if (rownum < startIndex)
                    {
                        var line = new string[columns];
                        lines.Add(line);
                    }
                }
                rownum++;
                row = worksheet.GetRow(rownum);
            }
            return lines;
        }
        /// <summary>
        /// close excel workbook
        /// </summary>
        /// <param name="workbook"></param>
        public static void CloseWorkbook(IWorkbook workbook)
        {
            workbook.Close();
        }

        //创建excel

        public static IWorkbook CreateWorkbook()
        {
            IWorkbook workbook = null;
            try
            {
                //创建一个工作簿
                workbook = new HSSFWorkbook();

            }
            catch (Exception ex)
            {

            }
            return workbook;

        }

        public static ISheet CreateSheetByName(IWorkbook workbook, string sheetName)
        {
            ISheet worksheet = null;
            if (workbook != null)
            {               
                worksheet = workbook.CreateSheet(sheetName);              
            }
           
            return worksheet;
        }

        //设置表头格式函数
        public static ICellStyle GetTitleCellStyle(IWorkbook workbook,short height,short colorIndex)
        {
            ICellStyle cell1Style = workbook.CreateCellStyle();
           
            cell1Style.BorderBottom = BorderStyle.Thin;
            cell1Style.BorderTop = BorderStyle.Thin;
            cell1Style.BorderLeft = BorderStyle.Thin;
            cell1Style.BorderRight = BorderStyle.Thin;

            cell1Style.Alignment = HorizontalAlignment.Center;
            cell1Style.VerticalAlignment = VerticalAlignment.Center;

            IFont font = workbook.CreateFont(); //设置字体大小和颜色
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = height;
            font.Color = colorIndex;
            cell1Style.SetFont(font);
            //cell1Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
            //cell1Style.FillPattern = FillPattern.SolidForeground;
            return cell1Style;
        }

        //设置单元格格式函数，边框粗细3个可选
        public static ICellStyle GetCellStyle(IWorkbook workbook, int borderThickness)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            NPOI.SS.UserModel.BorderStyle borderType;
            switch (borderThickness)
            {
                case 0:
                    borderType = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
                case 1:
                    borderType = NPOI.SS.UserModel.BorderStyle.Medium;
                    break;
                case 2:
                    borderType = NPOI.SS.UserModel.BorderStyle.Thick;
                    break;
                default:
                    borderType = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
            }
            cellStyle.BorderBottom = borderType;
            cellStyle.BorderTop = borderType;
            cellStyle.BorderLeft = borderType;
            cellStyle.BorderRight = borderType;

            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            IFont font = workbook.CreateFont();//设置字体大小和颜色
            font.FontName = "宋体";
            font.FontHeightInPoints = 10;
            cellStyle.SetFont(font);
            return cellStyle;
        }


        public static bool SaveExcel(IWorkbook workbook, string strPath)
        {
            try
            {
                if (File.Exists(strPath))
                {
                    File.Delete(strPath); 
                }

                FileStream file = new FileStream(strPath, FileMode.Create);
                workbook.Write(file);
                file.Close();
                workbook.Close();
            }
            catch(Exception ex)
            {
                return false;
            }

            return true;
        }

    }
}
